-----Function to get weather forcast based on threshold value of weather parameters

CREATE OR REPLACE FUNCTION dome.geteffectiveforcast4(
	coldmaxtemp double precision,
	coldmintemp double precision,
	hotmaxtemp double precision,
	hotmintemp double precision,
	windmax double precision,
	windmin double precision,
	rainmax double precision,
	rainmin double precision,
	humiditymax double precision,
	humiditymin double precision,
	snowmax double precision,
	snowmin double precision)
    RETURNS SETOF "TABLE(lockey text, from_time timestamp without time zone, to_time timestamp without time zone, weathertype text, flood_height text, severity text)"
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE 
    ROWS 1000.0
AS $function$

BEGIN
  RETURN QUERY select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
               select "LocKey","Date" + "Time" as dt,
			   row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
			  ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
               from dome."AccuWeatherForcastHourly" where "Temperature"<0
			   order by "LocKey", "Date" + "Time") s
			   group by s."LocKey",s.grp 
UNION
			  select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
              select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
			  ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Temperature">=coldmintemp and "Temperature"<= coldmaxtemp 
              order by "LocKey", "Date" + "Time") s
			  group by s."LocKey",s.grp 
UNION
			  select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Hot Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
              select "LocKey","Date" + "Time" as dt,row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
              ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" where "Temperature" > hotmaxtemp
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp 
UNION
			  select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
              select "LocKey","Date" + "Time" as dt,
              row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
              ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
               from dome."AccuWeatherForcastHourly" 
              where "Temperature" between hotmintemp and hotmaxtemp 
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp 
UNION
			 select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Wind Speed" >windmax 
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp
UNION
			 select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Wind Speed" between windmin and windmax
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp
UNION 

			select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Rain' as WeatherType,
			s."Flood_height",s."Severity" from (select "LocKey","Date" + "Time" as dt,
			row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
            ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
			,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc
			where "Rain" >rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
			order by "LocKey", "Date" + "Time") s
			group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION

			 select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Rain' as WeatherType,s."Flood_height",s."Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
             ,fc."Flood_height",fc."Severity" from dome."AccuWeatherForcastHourly",dome.flood_config2 fc 
              where "Rain" between rainmin and rainmax and "Rain" between fc."Accuweather_MinRain" and fc."Accuweather_MaxRain" 
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
			select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Snow" >snowmax
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp
UNION		
			 select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Snow" between snowmin and snowmax
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp
UNION 
			select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Humidity" >humiditymax
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp
UNION
			select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Amber Humidity' as WeatherType,'' as "Flood_height",'' as "Severity" from (
             select "LocKey","Date" + "Time" as dt,
             row_number() over (partition by "LocKey" order by "Date" + "Time") as rn,
             ("Date" + "Time" - (concat(row_number() over (partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
              from dome."AccuWeatherForcastHourly" 
              where "Humidity" between humiditymin and humiditymax
              order by "LocKey", "Date" + "Time") s
              group by s."LocKey",s.grp;
  

END;

$function$;


-----Query executing the above Weather Forcast function


Select GA.*,forcast.from_time::date as fromdate,forcast.from_time::time as fromtime,forcast.to_time::date as todate,forcast.to_time::time as totime,forcast.weathertype,forcast.flood_height,forcast.severity as flood_severity from dome.gridassetinfo3 GA ,dome.geteffectiveforcast4({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})  forcast where forcast.lockey = cast(GA.lockey as text)




---Function to get lightning impacted asset information



CREATE OR REPLACE FUNCTION dome.getlightningimpactedassets(
	asset_table text,
	buffer double precision)
    RETURNS SETOF "TABLE(lightningdate date, lightningtime time without time zone, latitude double precision, longitude double precision, amplitude double precision, distance double precision, feedername character varying, assetid bigint, assetcategory character varying, asset_xy character varying, customer_xy character varying)"
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE 
    ROWS 1000.0
AS $function$
BEGIN
  RETURN QUERY Select light.*,ga.feedername,ga.assetid,ga.assetcategory,ga.asset_xy,ga.customer_xy from dome.lightning light join dome.gridassetinfo3 ga
				on st_intersects(st_buffer(st_point(light.latitude,light.longitude),buffer),ST_GeomFromText('MULTIPOINT('||ga.asset_xy||')'))
				where ga.assettable=asset_table; 
END;

$function$;